{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "from scipy.stats import ttest_ind\n",
    "from scipy.stats import shapiro\n",
    "from  utils import column_letter_to_index\n",
    "import matplotlib.pyplot as plt\n",
    "import numpy as np\n",
    "from scipy import stats\n",
    "from scipy.stats import mannwhitneyu\n",
    "from scipy.stats import linregress\n",
    "#数据读取\n",
    "data_path = '../data/raw_data/334份 按选项序号 汇总变量后.xlsx'\n",
    "\n",
    "df = pd.read_excel(data_path)  "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "--年龄---\n",
      "count    334.000000\n",
      "mean       3.491018\n",
      "std        1.897899\n",
      "min        1.000000\n",
      "25%        2.000000\n",
      "50%        3.000000\n",
      "75%        5.000000\n",
      "max       12.000000\n",
      "Name: 14、每天上网小时, dtype: float64\n",
      "----电子健康素养---\n",
      "count    334.000000\n",
      "mean      23.077844\n",
      "std        8.888021\n",
      "min        8.000000\n",
      "25%       16.000000\n",
      "50%       24.000000\n",
      "75%       31.000000\n",
      "max       40.000000\n",
      "Name: 15、电子健康素养得分, dtype: float64\n"
     ]
    }
   ],
   "source": [
    "\n",
    "x_idx = column_letter_to_index('AK')#X变量\n",
    "ele_idx = column_letter_to_index('AT')#Y变量\n",
    "#-----------------------------------------------------------------\n",
    "print('--年龄---')\n",
    "print(df.iloc[:,x_idx].describe())\n",
    "'''\n",
    "年龄列的描述性统计：使用describe()函数计算年龄列的均值、标准差、中位数、最小值和最大值等统计指标。\n",
    "'''\n",
    "\n",
    "print('----电子健康素养---')\n",
    "print(df.iloc[:,ele_idx].describe())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "皮尔逊相关系数: 0.30553004574309\n",
      "斯皮尔曼相关系数: 0.32805204965068785\n"
     ]
    }
   ],
   "source": [
    "pearson_corr = df.iloc[:,x_idx].corr(df.iloc[:,ele_idx], method='pearson')\n",
    "print(\"皮尔逊相关系数:\", pearson_corr)\n",
    "\n",
    "spearman_corr = df.iloc[:,x_idx].corr(df.iloc[:,ele_idx], method='spearman')\n",
    "print(\"斯皮尔曼相关系数:\", spearman_corr)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "皮尔逊相关系数为-0.149，表明年龄和电子健康素养之间存在一个弱的负相关关系。这意味着年龄增长可能会略微降低电子健康素养得分。\n",
    "斯皮尔曼相关系数为-0.202，也表明年龄和电子健康素养之间存在一个弱的负相关关系。这与皮尔逊相关系数的结果一致。"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "回归系数 (斜率): 1.4308229162506474\n",
      "截距: 18.082815807340552\n",
      "相关系数 (R-value): 0.30553004574308995\n",
      "p-value: 1.2002168825173872e-08\n",
      "标准误差: 0.24472767622749886\n"
     ]
    }
   ],
   "source": [
    "# 将年龄和电子健康素养作为自变量和因变量\n",
    "X = df.iloc[:,x_idx].values  # 自变量（年龄）\n",
    "y = df.iloc[:,ele_idx].values  # 因变量（电子健康素养）\n",
    "\n",
    "# 执行线性回归\n",
    "slope, intercept, r_value, p_value, std_err = linregress(X, y)\n",
    "\n",
    "# 打印回归系数和相关统计信息\n",
    "print(f\"回归系数 (斜率): {slope}\")\n",
    "print(f\"截距: {intercept}\")\n",
    "print(f\"相关系数 (R-value): {r_value}\")\n",
    "print(f\"p-value: {p_value}\")\n",
    "print(f\"标准误差: {std_err}\")\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "根据您提供的结果，线性回归模型的方程可以表示为：\n",
    "\n",
    "y = -0.1997838272424466 * x + 36.91855682204482\n",
    "\n",
    "其中，y是因变量（电子健康素养），x是自变量（年龄），-0.1997838272424466是回归系数（斜率），36.91855682204482是截距。\n",
    "\n",
    "相关系数 (R-value)为-0.14943224101098382，表示年龄和电子健康素养之间存在弱负相关关系。p-value为0.006216940836631335，小于显著性水平（通常为0.05），说明该关系在统计上是显著的。\n",
    "\n",
    "标准误差为0.0725509532284283，表示预测值与真实值之间的平均差异。"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "whj",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.12.0"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
